import pymysql

db_name = 'auto_videos'
db_host = '127.0.0.1'
db_user = 'root'
db_password = 'root1234'
db_charset = 'utf8'
db_port = 3306

def createDB(is_new=False):
    try:
        db = pymysql.connect(host=db_host, user=db_user,
                             password=db_password, charset=db_charset)
        cursor = db.cursor()
        cursor.execute('show databases')
        rows = cursor.fetchall()
        for row in rows:
            tmp = "%2s" % row
            if db_name == tmp:
                if is_new == True:
                    cursor.execute('drop database if exists ' + db_name)
        cursor.execute('create database if not exists ' + db_name)
        db.commit()
        print('创建【{}】数据库成功'.format(db_name))
        return True
    except Exception as e:
        print('创建【{}】数据库失败: {}'.format(db_name, str(e)))
        return False


class database:
    def __init__(self, dbname=None, dbhost=None):
        if dbname is None:
            self._dbname = db_name
        else:
            self._dbname = dbname
        if dbhost is None:
            self._dbhost = db_host
        else:
            self._dbhost = dbhost

        self._dbuser = db_user
        self._dbpassword = db_password
        self._dbcharset = db_charset
        self._dbport = db_port

        self._conn = self.connectMySQL()

        if (self._conn):
            self._cursor = self._conn.cursor()

    # 连接
    def connectMySQL(self):
        conn = False
        try:
            conn = pymysql.connect(host=self._dbhost,
                                   user=self._dbuser,
                                   passwd=self._dbpassword,
                                   db=self._dbname,
                                   charset=self._dbcharset)
        except Exception as e:
            print('连接【{}】数据库失败: e'.format(db_name, e))
            conn = False
        return conn

    # 关闭连接
    def closeMySQL(self):
        flag = False
        if (self._conn):
            try:
                if (type(self._cursor) == 'object'):
                    self._cursor.close()
                if (type(self._conn) == 'object'):
                    self._conn.close()
                flag = True
            except Exception as e:
                print('关闭连接失败: {}'.format(e))
                flag = False
        return flag

    # 创建表
    def createTable(self, table, sql, is_new=False):
        flag = False
        if self._conn:
            try:
                # 如果表存在则删除
                if is_new:
                    self._cursor.execute('drop table if exists ' + table)
                self._cursor.execute(sql)
                flag = True
            except Exception as e:
                print('创建【{}】数据表失败: {}'.format(table, e))
                flag = False
        return flag

    # 查询
    def fetch(self, sql, page=1, page_size=None):
        extSql = ""
        if page_size != None:
            extSql = extSql + " limit {},{}".format(page, page_size)
        res = []
        # print(sql+extSql)
        if (self._conn):
            try:
                self._cursor.execute(sql+extSql)
                res = self._cursor.fetchall()
            except Exception as e:
                print('查询数据【{}】失败: {}'.format(sql+extSql, e))
                res = []
        return res

    # 更新
    def update(self, sql):
        if (self._conn):
            try:
                self._cursor.execute(sql)
                self._conn.commit()
                flag = True
            except Exception as e:
                print('更新数据【{}】失败: {}'.format(sql, e))
                flag = False
        return flag

    # 插入
    def insert(self, sql):
        flag = False
        if (self._conn):
            try:
                self._cursor.execute(sql)
                self._conn.commit()
                flag = True
            except Exception as e:
                print('插入数据【{}】失败: {}'.format(sql, e))
                flag = False
        return flag

    # 常用
    def commonUpdate(self, table, id, params):
        arrKey = params.keys()
        valueStr = ''
        for key in arrKey:
            if params[key] != None:
                item = "`" + key + "`" + ' = ' + "'" + str(params[key]) + "'"
                if valueStr == '':
                    valueStr = item
                else:
                    valueStr = valueStr + ', ' + item
        if len(valueStr) > 0:
            sql = "update %s set %s where id = '%d'" % (
                table, valueStr, int(id))
            self.update(sql)

    def commonFetch(self, table, params):
        sql_where = ""
        if params != None:
            query = ""
            for k, v in params.items():
                if v:
                    query += k + " = " + "'" + str(v) + "'" + " and "
            sql_where = " where " + query.rstrip(" and ")

        sql = """select * from """ + table + sql_where
        list = self.fetch(sql)
        return list

    def commonInsert(self, table, params):
        data = {}
        for key in params:
            if params[key] is not None:
                data[key] = params[key]

        ls = list(data)
        sql = "insert {} ({}) values ({})".format(table, ','.join(
            ls), ','.join(['%({})r'.format(field) for field in ls]))
        sql = sql % data
        self.insert(sql)

    def commonExist(self, table, params):
        sql_where = ""
        if params != None:
            query = ""
            for k, v in params.items():
                if v:
                    query += k + " = " + "'" + str(v) + "'" + " and "
            sql_where = " where " + query.rstrip(" and ")
        sql_where += " limit 1"
        sql = """select 1 from """ + table + sql_where
        r = self.fetch(sql)
        if r:
            return True
        return False

    def commonExistData(self, table, params):
        sql_where = ""
        if params != None:
            query = ""
            for k, v in params.items():
                if v:
                    query += k + " = " + "'" + str(v) + "'" + " and "
            sql_where = " where " + query.rstrip(" and ")
        sql_where += " limit 1"
        sql = """select * from """ + table + sql_where
        r = self.fetch(sql)
        if r:
            return r[0]
        return None

    def commonNumber(self, table, params):
        sql_where = ""
        if params != None:
            query = ""
            for k, v in params.items():
                if v:
                    query += k + " = " + "'" + str(v) + "'" + " and "
            sql_where = " where " + query.rstrip(" and ")
        sql = """select count(1) from """ + table + sql_where
        ret_cout = self.fetch(sql)
        count = ret_cout[0][0]
        return count
